#!/usr/bin/env python
# -*- coding: utf-8 -*-
# 时间     : 2021-06-30 17:23
# 作者     : DL (584054432@qq.com)
# 网址     : https://gitee.com/dingliangPython/
# 软件版本 : Python3.7.3
# 功能     ：

import logging
import os
import sys

import cx_Oracle
import pymysql
import pyodbc

import time_ext
from mysql_error import error_code

if sys.version_info.major == 3 and sys.version_info.minor <= 4:
    from DBUtils.PooledDB import PooledDB
else:
    from dbutils.pooled_db import PooledDB


class Connect(object):
    """
    MYSQL数据库对象，负责产生数据库连接 , 此类中的连接采用连接池实现获取连接对象：conn = Connect.getConn()
            释放连接对象;conn.close()或del conn
    """
    # 连接池对象
    __pool = None

    def __init__(self, config):
        # 数据库构造函数，从连接池中取出连接，并生成操作游标
        self.log = logging.getLogger('main.mysql_client')
        self.config = config
        self.type = self.config.get('type')

        self.error_code = error_code

    # 获取短连接
    def get_connect(self):
        mode = self.config.get('mode')
        if mode == 'ODBC' or mode == '2':
            return self.__get_odbc_connect()
        if mode == '短连接' or mode == '1':
            return self.__get_connect()
        if mode == '连接池' or mode == '0':
            return self.__get_pool_connect()

    # 获取点位符号
    def get_fmt(self, i = None):
        _type = self.type.lower()
        if _type == "oracle":
            return ":{}".format(i + 1)
        if _type in ["sqlite", 'access']:
            return "?"
        if _type in ("sqlserver", "mysql", 'mssql', 'sql server'):
            return "%s"

    # 获取odbc连接
    def __get_odbc_connect(self):
        try:
            dsn = "DSN={};UID={};PWD={};".format(self.config.get('dsn'), self.config.get('username'),
                                                 self.config.get('password', ''))
            connect = pyodbc.connect(dsn, autocommit = True, timeout = 1)
            return connect
        except Exception as e:
            self.get_error_info(e)

    # 获取连接池连接
    def __get_pool_connect(self):
        """
        @summary: 静态方法，从连接池中取出连接
        @return MySQLdb.connection
        """
        try:
            if self.__pool is None:
                self.__pool = PooledDB(creator = pymysql, mincached = 1, maxcached = 20,
                                       host = self.config.get('host'), port = int(self.config.get('port', 3306)),
                                       user = self.config.get('username'), passwd = self.config.get('password'),
                                       db = self.config.get('database'),
                                       charset = self.config.get('charset', 'utf8'))
                self.log.warning("初始化{}，连接池成功。。。".format(self.config.get('type')))
            return self.__pool.connection()
        except Exception as e:
            self.__pool = None
            self.get_error_info(e)

    # 获取数据库连接
    def __get_connect(self):
        try:
            connect = pymysql.connect(user = self.config.get('username'), password = self.config.get('password'),
                                      host = self.config.get('host'), port = int(self.config.get('port', 3306)),
                                      database = self.config.get('database'),
                                      charset = self.config.get('charset', 'utf8'),
                                      connect_timeout = 1, autocommit = True, read_timeout = 1, write_timeout = 1
                                      )
            return connect
        except Exception as e:
            self.get_error_info(e)

    # 查询数据
    def loadSql(self, sql, result_dict = None):
        conn = self.get_connect()
        if not conn: return
        cursor = conn.cursor()
        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            if data and len(data) >= 1:
                # 返回字典
                if result_dict:
                    field = [d[0] for d in cursor.description]
                    data = [{field[i]: value for i, value in enumerate(row)} for row in data]

                return data
            else:
                return 0
        except Exception  as e:
            self.get_error_info(e, sql)
        finally:
            cursor.close()
            conn.close()

    def load_sql(self, sql, result_dict = None):
        return self.loadSql(sql, result_dict)

    # 获取异常信息
    def get_error_info(self, e, sql = None, value = None):
        name = self.config.get('name')
        if e and e.args:
            index = e.args[0][0] if isinstance(e.args[0], tuple) else e.args[0]
            index = index.code if isinstance(index, cx_Oracle._Error) else index
            info = self.error_code.get(index)
            self.log.warning("连接{},异常：{},{} {} {}".format(name, index, info or e, sql or '', value or ''))
        else:
            self.log.warning("连接{},请检测查询语句，异常：{} {} {}".format(name, e, sql or '', value or ''))

    # 执行 插入，更新，删除等操作语句
    def execSql(self, sql, value_list = None):
        conn = self.get_connect()
        if not conn: return
        cursor = conn.cursor()
        try:
            if not value_list:
                cursor.execute(sql)
                result = cursor.rowcount
                return result
            else:
                cursor.executemany(sql, value_list)
                result = cursor.rowcount
                return result
        except Exception  as e:
            self.get_error_info(e)
        finally:
            conn.commit()
            cursor.close()
            conn.close()

    def exec_sql(self, sql, value_list = None):
        return self.execSql(sql, value_list)

    def rollback(self):
        conn = self.get_connect()
        if not conn: return
        conn.rollback()
        conn.commit()
        conn.close()
        self.log.warning("执行数据异常，回滚数据。。。")

    def get_datetime_str(self):
        _type = self.type.lower()
        if _type in ["mysql", 'access']:
            return "NOW()"
        if _type in ("oracle", "sqlserver", "mysql", 'mssql', 'sql server', "sqlite"):
            return "CURRENT_TIMESTAMP"

    # 测试网络
    def test_network(self):
        sql = " SELECT {}  "
        sql = "{} FROM DUAL".format(sql) if self.type.lower() == 'oracle' else sql
        result = self.loadSql(sql)
        if result:
            print(11111111, result)
            return True
        else:
            self.log.warning("数据库连接：{name}，类型：{type}，请检测连接参数及网络。。。".format(**self.config))
    # 获取带时间的数据库库
    def get_file_path(self):
        path = self.config.get('databasepath')
        file_path, file_name = os.path.split(path)
        # 时间格式串
        ext = ""
        if self.config.get('format'):
            now = time_ext.get_datetime()
            y = "{:0>4}".format(now.year)
            m = "{:0>2}".format(now.month)
            d = "{:0>2}".format(now.day)
            ext = self.config.get('format').replace('Y', y).replace('m', m).replace('d', d)
        # 去除时间格式
        size = len(ext)
        if self.config.get('left') != '0':
            file_name = file_name[size:]
        if self.config.get('right') != '0':
            name, _ext = os.path.splitext(file_name)
            name = name[:len(name) - size]
            file_name = name + _ext
        # 附加左边时间格式
        if ext and self.config.get('left') != '0':
            path = "{}/{}{}".format(file_path, ext, file_name)
            return path
        # 附加右边时间格式
        if ext and self.config.get('right') != '0':
            name, _ext = os.path.splitext(file_name)
            path = "{}/{}{}{}".format(file_path, name, ext, _ext)
            return path
        # 非时间格式
        return path


if __name__ == '__main__':
    import time

    config = {
            'username': 'ygct',
            # 'mode': '短连接',
            'mode': 'ODBC',
            'type': 'mysql',
            'name': 'ics',
            'host': '127.0.0.1',
            'dsn': 'MYSQL',
            'port': 3306,
            'password': 'ygct',
            'database': 'ygct',
            # 'charset': 'gbk'
            }
    client = Connect(config)
    sql = "SELECT * FROM rt_data "
    while 1:
        a = client.loadSql(sql, True)
        print(a)
        time.sleep(3)